package mhserver;

import java.awt.Component;
import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.JOptionPane;

import mh.log.MHLogger;

import org.hsqldb.Server;

/**
 * Handles all database connection and messages.
 * 
 * @author Gianmarco Laggia
 * @version 1.1.0
 */
public class Database {

	private static final Logger	LOG	= Logger.getLogger(Database.class.getName());
	/**
	 * The database server class
	 */
	private static Server		hsqlServer;

	/**
	 * The connection class. Used to manage request to the database.
	 */
	private static Connection	connection;

	/**
	 * The name of the database
	 */
	private static String		dbName;

	/**
	 * Constructor.<br>
	 * Create a new <code>Database</code> connected to a specified hsqldb and saved in a specified file.<br>
	 * Defaults sets logs to null e logWriter to null.
	 * 
	 * @param databaseName
	 *            The name of the database to connect
	 * @param fileName
	 *            The name of the file where the database is saved.
	 */
	public Database(String databaseName, String fileName) {

		LOG.setLevel(Level.FINEST);

		hsqlServer = null;
		connection = null;

		dbName = databaseName;

		// ###### LOGGER ######
		LOG.finest("Creating a new database server class");
		// ####################
		hsqlServer = new Server();

		// HSQLDB prints out a lot of informations when starting and closing, which we don't need now.
		// Normally you should point the setLogWriter to some Writer object that could store the logs.
		// hsqlServer.putPropertiesFromFile("conf/logger.properties");
		hsqlServer.setLogWriter(null);
		hsqlServer.setSilent(true);

		// settings and data will be stored in files
		// testdb.properties and testdb.script
		hsqlServer.setDatabaseName(0, dbName);
		hsqlServer.setDatabasePath(0, "file:db/" + fileName);
	}

	/**
	 * Start the new database class.
	 */
	public static void start() {

		// ###### LOGGER ######
		LOG.config("Starting a new database class");
		// ####################
		hsqlServer.start();
		connection = null;
	}

	/**
	 * Stop the database class.
	 */
	public static void stop() {
		try {
			// ###### LOGGER ######
			LOG.finest("Stopping the database");
			// ####################
			if (connection != null) {
				connection.close();
			}
			if (hsqlServer != null) {
				hsqlServer.stop();
			}
			// ###### LOGGER ######
			LOG.finest("Done");
			// ####################
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to stop the databse class");
			// ####################
		}
	}

	/**
	 * Start the new connection to the specified database, using the default username "sa" e no password
	 */
	public static void connect() {
		try {
			// ###### LOGGER ######
			LOG.finest("Getting a connection to the newly started database");
			// ####################
			Class.forName("org.hsqldb.jdbcDriver");
			// Default user of the HSQLDB is 'sa' with an empty password
			connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/garby", "sa", "");
		} catch (ClassNotFoundException e) {
			// ###### LOGGER ######
			LOG.severe("Impossible to connect to the database. ClassNotFound");
			// ####################
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.finest("Impossible to connect to the database. " + e.getCause());
			// ####################
		}
	}

	/**
	 * Start the new connection to the specified database, using the default username "sa" e no password
	 */
	public static void disconnect() {
		try {
			// ###### LOGGER ######
			LOG.finest("Stopping the connection to the database");
			// ####################
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to stop the connection to the database. " + e.getCause());
			// ####################
		}
	}

	/**
	 * Create the basic structure of the database if necessary.<br>
	 * This must be called only once, otherwise the database and all the data in it will be lost.
	 * 
	 * TODO read the structure from an XML
	 */
	public static void tableStructureCreation() {
		try {
			if (!okcancel("Maybe you are deleting all the old database. Sure?")) {
				return;
			}
			// ###### LOGGER ######
			LOG.finest("Dropping all existing tables");
			// ####################
			connection.prepareStatement("DROP SCHEMA PUBLIC CASCADE").execute();

			// ###### LOGGER ######
			LOG.finest("Setting database default type (chached)");
			// ####################
			connection.prepareStatement("SET DATABASE DEFAULT TABLE TYPE CACHED;").execute();

			// ###### LOGGER ######
			LOG.finest("Creating table 'user'");
			// ####################
			connection
					.prepareStatement(
							"CREATE TABLE user (user_id INTEGER IDENTITY, nickname VARCHAR(40) UNIQUE, password VARCHAR(64), salt VARCHAR(64), gender TINYINT, monstertype TINYINT, spriteset VARCHAR(32), spritenum INT, level INT, experience INT, money INT, healt INT, healt_max INT, magicpw INT, magicpw_max INT, schoolreport_id INT, personalroom_id INT, map_id INT, map_pos VARCHAR(10) );")
					.execute();

			// ###### LOGGER ######
			LOG.finest("Creating table 'schoolreport'");
			// ####################
			connection
					.prepareStatement(
							"CREATE TABLE schoolreport (schoolreport_id INTEGER IDENTITY, user_id INTEGER, data TINYINT, potions TINYINT, transfiguration TINYINT, charms TINYINT, comc TINYINT, arithmancy TINYINT, ancientrunes TINYINT, hom TINYINT, divination TINYINT, herbology TINYINT, mugglestudies TINYINT, flying TINYINT, astronomy TINYINT);")
					.execute();

			// ###### LOGGER ######
			LOG.finest("Creating table 'map'");
			// ####################
			connection.prepareStatement("CREATE TABLE map (map_id INTEGER IDENTITY, socketadd VARCHAR(100));").execute();

			// ###### LOGGER ######
			LOG.finest("Creating table 'personalroom'");
			// ####################
			connection.prepareStatement("CREATE TABLE personalroom (personalroom_id INTEGER IDENTITY, maphash VARCHAR(64));").execute();

			// ###### LOGGER ######
			LOG.finest("Creating table 'persistentcharm'");
			// ####################
			connection
					.prepareStatement(
							"CREATE TABLE persistentcharm (persistent_id INTEGER IDENTITY,user_id INT, map_id INT, map_pos VARCHAR(10), level INT, schoolrep_val INT);")
					.execute();

			// ###### LOGGER ######
			LOG.config("Structure created!");
			// ####################

		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.severe("Impossible to complete the creation of the structure" + e.getCause());
			// ####################
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * @param fileName
	 * @param tabName
	 */
	public static void populateFromFile(String fileName, String tabName) {
		try {

			FileInputStream fstream = new FileInputStream(fileName);
			// Get the object of DataInputStream
			DataInputStream in = new DataInputStream(fstream);
			BufferedReader br = new BufferedReader(new InputStreamReader(in));
			String strLine;
			ArrayList<String> columns = listTableColumns(tabName);
			while ((strLine = br.readLine()) != null) {
				if (columns.size() == strLine.split("!").length) {
					String[] lineSplitted = strLine.split("!");
					String names = "";
					String values = "";
					for (int i = 0; i < columns.size(); i++) {
						names += columns.get(i) + ",";
						values += "'" + lineSplitted[i] + "',";
					}
					connection.prepareStatement(
							"insert into " + tabName + "(" + names.substring(0, names.length() - 1) + ") values (" + values.substring(0, values.length() - 1)
									+ ");").execute();
				}
			}
			// Close the input stream
			in.close();
		} catch (Exception e) {
			System.err.println("Error: " + e.getMessage());
		}
	}

	/**
	 * Insert a new special field in the correct table.<br>
	 * 
	 * @param name
	 *            The name of the new special field
	 * @return true if all is ok.
	 */
	public static boolean addSpecialData(String name) {
		// ###### LOGGER ######
		LOG.finest("Adding a new Service");
		// ####################
		try {
			connection.prepareStatement("INSERT INTO specialdata (name) VALUES ('" + name + "');").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.severe("Impossible to add the new special field " + e);
			// ####################
			return false;
		}
	}

	/**
	 * Edit an existing special field<br>
	 * 
	 * @param id
	 *            The id of the field to edit
	 * @param name
	 *            The new name to set
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	public static boolean editSpecialData(int id, String name) {
		// ###### LOGGER ######
		LOG.finest("Editing the special field id:" + id);
		// ####################
		try {
			connection.prepareStatement("UPDATE specialdata SET name='" + name + "' WHERE id=" + id + ";").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to edit the special field " + e.getCause());
			// ####################
			return false;
		}
	}

	/**
	 * Edit an existing special field<br>
	 * 
	 * @param old
	 *            The name of the field to edit
	 * @param newn
	 *            The new name to set
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	public static boolean editSpecialData(String old, String newn) {
		// ###### LOGGER ######
		LOG.finest("Editing the special field name:" + old);
		// ####################
		try {
			connection.prepareStatement("UPDATE specialdata SET name='" + newn + "' WHERE name='" + old + "';").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to edit the special field " + e.getCause());
			// ####################
			return false;
		}
	}

	/**
	 * Delete an existing special field.
	 * 
	 * @param id
	 *            The id of the special field to delete.
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	public static boolean deleteSpecialData(int id) {
		// ###### LOGGER ######
		LOG.finest("Deleting the special field id:" + id);
		// ####################
		try {
			connection.prepareStatement("DELETE FROM specialdata WHERE id=" + id + ";").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to delete the special field " + e.getCause());
			// ####################
			return false;
		}
	}

	/**
	 * Delete an existing special field.
	 * 
	 * @param name
	 *            The name of the special field to delete.
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	public static boolean deleteSpecialData(String name) {
		// ###### LOGGER ######
		LOG.finest("Deleting the special field name:" + name);
		// ####################
		try {
			connection.prepareStatement("DELETE FROM specialdata WHERE name='" + name + "';").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to delete the special field " + e.getCause());
			// ####################
			return false;
		}
	}

	/**
	 * This method return the list of all the special fields.
	 * 
	 * @return an <code>ArrayList&lt;String&gt;</code> of all the Special fields
	 */
	public static ArrayList<String> listSpecialFields() {
		// ###### LOGGER ######
		LOG.finest("Getting the special field List");
		// ####################
		ArrayList<String> specialFieldList = new ArrayList<String>();
		try {
			ResultSet rs = connection.prepareStatement("SELECT * FROM specialData;").executeQuery();
			while (!rs.isLast()) {
				rs.next();
				specialFieldList.add(rs.getString(2));
			}
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.info("Error in getting the special field List " + e.getCause());
			// ####################
			return null;
		}
		return specialFieldList;
	}

	/**
	 * Insert a what-have-been-done row.<br>
	 * 
	 * @param clId
	 *            Id of the client that received the service
	 * @param srId
	 *            Id of the service that the client received.
	 * @param date
	 *            Today
	 * 
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	public static boolean insertDone(int clId, int srId, Date date) {
		// ###### LOGGER ######
		LOG.finest("Something has been done, registering it into the database.");
		// ####################
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			connection.prepareStatement("INSERT INTO done (client, service, date) VALUES (" + clId + ", " + srId + ", '" + df.format(date) + "');").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.severe("Impossible to add the new Service " + e.getCause());
			e.printStackTrace();
			// ####################
			return false;
		}
	}

	public static void printDone() {
		// ###### LOGGER ######
		LOG.finest("Getting the Done List");
		// ####################
		ArrayList<String> value = new ArrayList<String>();
		try {
			ResultSet rs = connection.prepareStatement("SELECT * FROM done;").executeQuery();
			while (!rs.isLast()) {
				rs.next();
				String row = "";
				row += "\n\nClientID: " + rs.getInt(2);
				row += "\nServiceID: " + rs.getInt(3);
				row += "\nDate: " + rs.getTimestamp(4);
				value.add(row);
			}
			System.out.println(value);
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Error in getting the settings list " + e.getCause());
			// ####################
		}
	}

	/**
	 * Insert a new service in the correct table.<br>
	 * 
	 * @param name
	 *            The name of the new service
	 * @param price
	 *            The price of the new service
	 * @param cadence
	 *            The cadence of the new service
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	public static boolean addService(String name, BigDecimal price, int cadence) {
		// ###### LOGGER ######
		LOG.finest("Adding a new Service");
		// ####################
		try {
			connection.prepareStatement("INSERT INTO services (name, price, cadence) VALUES ('" + name + "', " + price + ", " + cadence + ");").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.severe("Impossible to add the new Service " + e.getCause());
			// ####################
			return false;
		}
	}

	/**
	 * Edit an existing service<br>
	 * 
	 * @param id
	 *            The id of the service to edit
	 * @param name
	 *            The new name to set
	 * @param price
	 *            The new price to set
	 * @param cadence
	 *            The new cadence to set
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	public static boolean editService(int id, String name, BigDecimal price, int cadence) {
		// ###### LOGGER ######
		LOG.finest("Editing the Service id:" + id);
		// ####################
		try {
			connection.prepareStatement("UPDATE services SET name='" + name + "', price=" + price + ", cadence=" + cadence + " WHERE id=" + id + ";").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to edit the Service " + e.getCause());
			// ####################
			return false;
		}
	}

	/**
	 * Delete an existing service.
	 * 
	 * @param id
	 *            The id of the service to delete.
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	public static boolean deleteService(int id) {
		// ###### LOGGER ######
		LOG.finest("Deleting the Service id:" + id);
		// ####################
		try {
			connection.prepareStatement("DELETE FROM services WHERE id=" + id + ";").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to delete the Service " + e.getCause());
			// ####################
			return false;
		}
	}

	//	/**
	//	 * This method return the list of all the services.
	//	 * 
	//	 * @return an <code>ArrayList&lt;Service&gt;</code> of all the <code>Service</code>s
	//	 */
	//	public static ArrayList<Service> listServices() {
	//		// ###### LOGGER ######
	//		LOG.finest("Getting the Service List");
	//		// ####################
	//		ArrayList<Service> serviceList = new ArrayList<Service>();
	//		try {
	//			ResultSet rs = connection.prepareStatement("SELECT * FROM services;").executeQuery();
	//			while (!rs.isLast()) {
	//				rs.next();
	//				Service sr = new Service(rs.getInt(1), rs.getString(2), rs.getBigDecimal(3), rs.getInt(4));
	//				serviceList.add(sr);
	//			}
	//		} catch (SQLException e) {
	//			// ###### LOGGER ######
	//			LOG.info("Error in getting the Service List " + e.getCause());
	//			// ####################
	//			return null;
	//		}
	//		return serviceList;
	//	}

	//	/**
	//	 * This method return the desired service with the specified id.
	//	 * 
	//	 * @param id
	//	 *            The id number
	//	 * 
	//	 * @return a <code>Service</code> object
	//	 */
	//	public static Service getServiceById(int id) {
	//		// ###### LOGGER ######
	//		LOG.finest("Getting the desired Service (id=" + id + ")");
	//		// ####################
	//		Service sr = null;
	//		try {
	//			ResultSet rs = connection.prepareStatement("SELECT * FROM services WHERE id=" + id + ";").executeQuery();
	//			while (!rs.isLast()) {
	//				rs.next();
	//				sr = new Service(rs.getInt(1), rs.getString(2), rs.getBigDecimal(3), rs.getInt(4));
	//			}
	//		} catch (SQLException e) {
	//			// ###### LOGGER ######
	//			LOG.info("Error in getting the Service List " + e.getCause());
	//			// ####################
	//			return null;
	//		}
	//		return sr;
	//	}

	/**
	 * Create a new client and insert it into the correct table.
	 * 
	 * @param name
	 *            Name of the client
	 * @param surname
	 *            Surname of the client
	 * @param address
	 *            The address of the client
	 * @param tel
	 *            Telephone number
	 * @param cell
	 *            Mobile phone number
	 * @param data
	 *            Various special data, divided by a '!'
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	public static boolean addClient(String name, String surname, String address, String tel, String cell, String... data) {
		// ###### LOGGER ######
		LOG.finest("Adding a new Client");
		// ####################
		try {
			String other = "";
			for (int i = 0; i < data.length; i++) {
				if (i != 0) {
					other += "!";
				}
				other += data[i];
			}
			DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
			Date date = new Date();
			connection.prepareStatement(
					"INSERT INTO clients (name,surname,address,tel,cell,created,data) VALUES ('" + name + "','" + surname + "', '" + address + "', '" + tel
							+ "', '" + cell + "','" + dateFormat.format(date) + "','" + other + "');").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.finest("Impossible to add a new Client " + e.getCause());
			// ####################
			return false;
		}
	}

	/**
	 * Edit an existing client.
	 * 
	 * @param id
	 *            The id of the client
	 * @param name
	 *            Name of the client
	 * @param surname
	 *            Surame of the client
	 * @param address
	 *            The address of the client
	 * @param tel
	 *            Telephone number
	 * @param cell
	 *            Mobile phone number
	 * @param last
	 *            Date of the last appointment
	 * @param next
	 *            Date of the next appointment
	 * @param data
	 *            Various special data, divided by a '!'
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 * 
	 */
	public static boolean editClient(int id, String name, String surname, String address, String tel, String cell, Date last, Date next, String... data) {

		// ###### LOGGER ######
		LOG.finest("Editing the Client id:" + id);
		// ####################
		DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			String other = "";
			for (int i = 0; i < data.length; i++) {
				if (i != 0) {
					other += "!";
				}
				other += data[i];
			}
			connection.prepareStatement(
					"UPDATE clients SET name='" + name + "', surname='" + surname + "', address='" + address + "', tel='" + tel + "', cell='" + cell
							+ "', last='" + dateFormat.format(last) + "', next='" + dateFormat.format(next) + "', data='" + other + "' WHERE id=" + id + ";")
					.execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to edit the Client " + e.getCause());
			// ####################
			return false;
		}
	}

	/**
	 * Set client last visit
	 * 
	 * @param id
	 * @param data
	 * @return true if all goes well
	 */
	public static boolean setClientLast(int id, Date data) {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		try {
			connection.prepareStatement("UPDATE clients SET last='" + df.format(data) + "' WHERE id=" + id + ";").execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Edit an existing client.
	 * 
	 * @param id
	 *            The id of the client
	 * @param address
	 *            The address of the client
	 * @param tel
	 *            Telephone number
	 * @param cell
	 *            Mobile phone number
	 * @param data
	 *            Various special data, divided by a '!'
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	@Deprecated
	public static boolean editClient(int id, String address, String tel, String cell, String data) {

		// ###### LOGGER ######
		LOG.finest("Editing the Client id:" + id);
		// ####################
		try {
			// String other = "";
			// for (int i = 0; i < data.length; i++) {
			// if (i != 0) {
			// other += "!";
			// }
			// other += data[i];
			// }
			connection.prepareStatement(
					"UPDATE clients SET address='" + address + "', tel='" + tel + "', cell='" + cell + "', data='" + data + "' WHERE id=" + id + ";").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to edit the Client " + e.getCause());
			// ####################
			return false;
		}
	}

	/**
	 * Delete an existing client.
	 * 
	 * @param id
	 *            The id of the service to delete.
	 * @return True if all it's done, false if any <code>SQLException</code> is catched.
	 */
	public static boolean deleteClient(int id) {
		// ###### LOGGER ######
		LOG.finest("Deleting the Client id:" + id);
		// ####################
		try {
			connection.prepareStatement("DELETE FROM clients WHERE id=" + id + ";").execute();
			return true;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Impossible to delete the Client " + e.getCause());
			// ####################
			return false;
		}
	}

	//	/**
	//	 * This method return the list of all the clients Ordered by Name.
	//	 * 
	//	 * @return an <code>ArrayList&lt;Client&gt;</code> of all the <code>Client</code>s
	//	 */
	//	public static ArrayList<Client> listClients() {
	//		// ###### LOGGER ######
	//		LOG.finest("Getting the Client List");
	//		// ####################
	//		ArrayList<Client> clientList = new ArrayList<Client>();
	//		try {
	//			ResultSet rs = connection.prepareStatement("SELECT * FROM clients ORDER BY surname, name ASC;").executeQuery();
	//			while (!rs.isLast()) {
	//				rs.next();
	//				Client cl = new Client(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),rs.getString(6), rs.getDate(7), rs.getTimestamp(8),
	//						rs.getTimestamp(9), rs.getString(10));
	//				clientList.add(cl);
	//			}
	//		} catch (SQLException e) {
	//			// ###### LOGGER ######
	//			LOG.info("Error in getting the Client List " + e.getCause());
	//			// ####################
	//			return null;
	//		}
	//		return clientList;
	//	}

	/**
	 * Return the value of the specific setting.
	 * 
	 * @param name
	 *            The name of the setting.
	 * @return A string, value of the specified setting.
	 */
	public static String listSettingsValuesByName(String name) {
		// ###### LOGGER ######
		LOG.finest("Getting the Setting List");
		// ####################
		String value = "";
		try {
			ResultSet rs = connection.prepareStatement("SELECT value FROM settings WHERE name='" + name + "';").executeQuery();
			while (!rs.isLast()) {
				rs.next();
				value = rs.getString(3);
			}
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Error in getting the settings list " + e.getCause());
			// ####################
			return null;
		}
		return value;
	}

	private static ArrayList<String> listTableColumns(String table) {
		try {
			// ###### LOGGER ######
			LOG.finest("Asking to the database the column list for " + table.toUpperCase());
			// ####################
			ArrayList<String> columnNames = new ArrayList<String>();
			ResultSet rsColumns = connection.prepareStatement(
					"SELECT COLUMN_NAME, TYPE_NAME, COLUMN_SIZE FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_NAME = '" + table.toUpperCase()
							+ "' AND COLUMN_NAME NOT LIKE 'ID' ORDER BY ORDINAL_POSITION").executeQuery();
			while (rsColumns.next()) {
				columnNames.add(rsColumns.getString("COLUMN_NAME"));
				// String columnType = rsColumns.getString("TYPE_NAME");
				// int size = rsColumns.getInt("COLUMN_SIZE");
				// int position = rsColumns.getInt("ORDINAL_POSITION");
			}
			return columnNames;
		} catch (SQLException e) {
			// ###### LOGGER ######
			LOG.warning("Error asking the column list for " + table.toUpperCase());
			// ####################
			return null;
		}
	}

	// public static ArrayList<String> listNamesFromTable(String table) {
	// try {
	// // ###### DEBUG ######
	// Console.println(CN + "Asking to the database the values list for " + table.toUpperCase());
	// // ####################
	// ArrayList<String> values = new ArrayList<String>();
	// ResultSet rs = connection.prepareStatement("select * from " + table + ";").executeQuery();
	// // Checking if the data is correct
	// while (!rs.isLast()) {
	// rs.next();
	// values.add(rs.getString(2));
	// }
	// return values;
	// } catch (SQLException e) {
	// // ###### DEBUG ######
	// Console.println(CN + "Error asking the values list for " + table.toUpperCase());
	// // ####################
	// return null;
	// }
	// }

	// public static boolean updateTable(String table, String name, String value) {
	// try {
	// connection.prepareStatement("UPDATE " + table + " SET value='" + value + "' WHERE name='" + name +
	// "';").execute();
	// return true;
	// } catch (SQLException e) {
	// return false;
	// }
	// }

	@SuppressWarnings("javadoc")
	public static void main(String[] args) {
		try {
			MHLogger.setup();
		} catch (Exception e) {

		}
		new Database("magichogwarts", "mhdb");
		Database.start();
		Database.connect();
		Database.tableStructureCreation();
		//			Database.populateFromFile("conf/populateServ.txt", "services");
		// Database.populateFromFile("conf/populateProd.txt", "products");
		// Database.populateFromFile("conf/populateSett.txt", "settings");
		//			System.out.println(Database.listServices());
		// d.doSomething();
		Database.stop();
	}

	// #############################################################
	@SuppressWarnings("javadoc")
	public static boolean okcancel(String theMessage) {
		int result = JOptionPane.showConfirmDialog((Component) null, theMessage, "alert", JOptionPane.OK_CANCEL_OPTION);
		if (result == 0) {
			return true;
		} else {
			return false;
		}
	}
	// #############################################################
}
